package org.gosdt.ew.db;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

import org.gosdt.ew.model.Alphabet;
import org.gosdt.ew.model.DataBaseAccess;

public class AlphabetDBAccess extends DataBaseAccess {

	public static AlphabetDBAccess create(Alphabet alphabet) {
		return new AlphabetDBAccess(alphabet);
	}

	private Alphabet alphabet;

	public AlphabetDBAccess(Alphabet alphabet) {
		super("Alphabet");
		this.alphabet = alphabet;

		// try to get letter from db if it's not in then add.
		HashMap<String, String> key = new HashMap<String, String>();
		key.put("letter", alphabet.getLetter());
		key.put("language", alphabet.getLanguage());
		if (getOneItemByKey(key) == null) {
			insertCurRecord();
		}
	}

	protected Alphabet getOneItemByKey(HashMap<String, String> keys) {
		try {
			ResultSet rs = getListByDetail(keys);
			int getItem = 0;
			while (rs.next()) {
				alphabet.setLanguageName(rs.getString("language"));
				alphabet.setLetter(rs.getString("letter"));
				alphabet.setWordsCount(rs.getInt("wordsCount"));
				getItem++;
				rs.close();
				break;
			}
			if (getItem == 0)
				return null;
			else
				return alphabet;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public ArrayList<Alphabet> getList(HashMap<String, String> keys) {
		ArrayList<Alphabet> list = new ArrayList<Alphabet>();
		try {
			ResultSet rs = getListByDetail(keys);
			while (rs.next()) {
				Alphabet item = new Alphabet(rs.getString("language"),
						rs.getString("letter"));
				item.setWordsCount(rs.getInt("wordsCount"));
				list.add(item);
			}
			rs.close();
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	protected final ResultSet getListByDetail(HashMap<?, ?> keys) {
		String selectSQL = "SELECT * From Alphabet ";
		if (keys.get("letter") != null) {
			selectSQL += "where letter = '" + keys.get("letter") + "'";
			if (keys.get("language") != null)
				selectSQL += " and language = '" + keys.get("language") + "'";
		} else {
			if (keys.get("language") != null)
				selectSQL += "where language = '" + keys.get("language") + "'";
		}

		try {
			EWDataBase db = EWDataBase.getInstance();
			return db.getConn().prepareStatement(selectSQL).executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	private int insertCurRecord() {
		HashMap<String, String> insertValues = new HashMap<String, String>();
		insertValues.put("LETTER".toUpperCase(), alphabet.getLetter());
		insertValues.put("language".toUpperCase(), alphabet.getLanguage());
		insertValues.put("wordsCount".toUpperCase(),
				"" + alphabet.getWordsCount());
		return super.insertOneRecord(insertValues);
	}
	
	public int updateCurrentRecord() {
		ArrayList<String> where = new ArrayList<String>();
		where.add("LETTER = '" + alphabet.getLetter() + "'");
		where.add("language = '" + alphabet.getLanguage() + "'");

		ArrayList<String> sets = new ArrayList<String>();
		sets.add("wordsCount = " + alphabet.getWordsCount());

		return super.updateOneRecord(where, sets);
	}
}
